package com.iweb.util;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class JdbcUtil {
    static Connection conn = null;//数据库的连接
    static Statement stat = null;//操作sql语句的对象
    static   PreparedStatement stat1=null;//更好用的操作数据库对象
    //查询相关
    static ResultSet rest = null;//查询语句执行的结果集
    static  ResultSetMetaData rsmd = null;//用来获取ResultSet中列相关的信息


    //加载驱动获取连接
    public   static Connection getConn() {
        try {
            //加载Orcale驱动类
            Class.forName("com.mysql.cj.jdbc.Driver");
            //获取数据库连接
            conn = DriverManager.getConnection("jdbc:mysql://192.168.77.100:3306/inventory_db?characterEncoding=utf-8", "root", "123456");
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        return conn;
    }
    //关闭各种对象
    public  static  void closeSrc(){
        try {
            if (rest != null) rest.close();
            if (stat != null) stat.close();
            if (conn != null) conn.close();
            if(stat1!=null)  stat1.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
    //查询方法
    public static List<Map<String, Object>> selectBySql(String sql) {
        List<Map<String, Object>> list = new ArrayList<>();
        try {
            conn = getConn();
            //获取Startment对象
            stat = conn.createStatement();
            rest = stat.executeQuery(sql);//结果集
            rsmd = rest.getMetaData();//列相关的
            while (rest.next()) {
                Map<String, Object> map = new HashMap<>();
                for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                    map.put(rsmd.getColumnName(i), rest.getObject(i));
                }
                list.add(map);

            }
        }catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            closeSrc();
        }
        return  list;
    }

    //查询方法优化
    public static<E>List<Map<String,Object>>  selectBySql(String sql,E...e) {
        List<Map<String, Object>> list = new ArrayList<>();
        try {
            conn = getConn();
            //获取Startment对象
            stat1 = conn.prepareStatement(sql);
            for (int i = 0; i < e.length; i++) {
                stat1.setObject(i + 1, e[i]);
            }
            rest = stat1.executeQuery();
            rsmd = rest.getMetaData();
            while (rest.next()) {
                Map<String, Object> map = new HashMap<>();
                for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                    map.put(rsmd.getColumnLabel(i), rest.getObject(i));//将getColumnName(i)修改为getColumnLabel(i)
                }
                list.add(map);
            }
        } catch (SQLException ex) {
            throw new RuntimeException(ex);
        }finally {
            closeSrc();
        }
        return list;
    }
    //增删改的方法
    public  static  int editBySql(String sql){
        conn=getConn();
        int result=0;
        //获取Statement对象
        try {
            stat=conn.createStatement();//获取statement对象
            result=stat.executeUpdate(sql);//增删改语句的sql
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            closeSrc();
        }
        return  result;
    }

    //增删改
    public  static <E> int editBySql(String sql,E...e){
        conn=getConn();
        int result=0;
        //获取statement对象
        try {
            stat1=conn.prepareStatement(sql);
            for(int i=0;i<e.length;i++){
                stat1.setObject(i+1,e[i]);
            }
            result=stat1.executeUpdate();
        } catch (SQLException ex) {
            throw new RuntimeException(ex);
        }finally {
            closeSrc();
        }
        return result;

    }
}
